/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import modelo.Lectura;
import dao.generic.GenericDAO;
import java.util.Date;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import modelo.GrupoLectura;
import modelo.Historial;
import modelo.Sustrato;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;

/**
 *
 * @author Administrador
 */
public class LecturaDAO extends GenericDAO {

  public Lectura getLecturabyId(int idSensorRegistro) {
    Lectura sr = null;
    try {
      iniciarOperacion();
      sr = (Lectura) sesion.get(Lectura.class, idSensorRegistro);

    } finally {
      sesion.close();
    }
    return sr;
  }

  public int ultimoRegistro() {
    Integer ultimo;
    try {
      iniciarOperacion();
      Query query = sesion.createQuery("SELECT MAX(id) FROM Lectura");
      ultimo = (Integer) query.uniqueResult();
      if (ultimo == null) {
        ultimo = 0;
      }
      System.out.println("El ultimo fue: " + ultimo);

    } finally {
      sesion.close();
    }
    return ultimo;
  }

  public List lecturaDia(Date fecha) {
    List lista = null;
    try {
      iniciarOperacion();
      Query query = sesion.createQuery("SELECT elements(h.lectura) FROM Historial as h where h.fecha= :fecha");
      query.setParameter("fecha", fecha);
      lista = query.list();
    } catch (HibernateException e) {
      System.out.println("A ocurrido un error " + e);
    }
    return lista;
  }

  public void cerrarSesionBeforeIterator() {
    if (sesion.isOpen()) {
      System.out.println("Se encuentra abierta la sesion");
      sesion.close();
    } else {
      System.out.println("No hay sesion abierta");
    }
  }

  public List LecturaDiaCriteria(Date fecha, String[] sustratos) {
    List lista = null;
    try {
      
      iniciarOperacion();
      DetachedCriteria fechaH = DetachedCriteria.forClass(Historial.class)
              .setProjection(Projections.max("fecha"));
      DetachedCriteria sustrato = DetachedCriteria.forClass(Sustrato.class)
              .add(Restrictions.in("nombre", sustratos))
              .setProjection(Property.forName("id"));
      
      Criteria criteria = sesion.createCriteria(Lectura.class,"l");
      criteria.createCriteria("l.historial","h");
      criteria.createCriteria("l.sustrato","s");
      criteria.add(Property.forName("h.fecha").in(fechaH));
      criteria.add(Property.forName("s.id").in(sustrato));
      criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
      
      lista = criteria.list();
      
      for(Object obj:lista){
        Lectura l=(Lectura) obj;
        System.out.println(l.getId());
      }
      
    } catch (HibernateException e) {
      System.out.println("Ha ocurrido un error" + e);
    }
    return lista;
  }

  public List LecturaDiaCriteria(Date fecha, String sustrato) {
    List lista = null;
    try {
      iniciarOperacion();
      Criteria criteria = sesion.createCriteria(Lectura.class);
      criteria.createCriteria("historial").add(Restrictions.eq("fecha", fecha));
      criteria.createCriteria("sustrato").add(Restrictions.eq("nombre", sustrato));
      lista = criteria.list();
    } catch (HibernateException e) {
      System.out.println("Ha ocurrido un error" + e);
    }
    return lista;
  }

  public Integer[] consultaUltHora(Date fecha) {
    Integer[] matriz = null;
    ArrayList<Integer> lista = new ArrayList<Integer>();
    Iterator i;
    try {
      iniciarOperacion();
      Query query = sesion.createQuery("select MAX(l.id), l.sustrato.id from Lectura as l where l.sustrato.id in (select id from Sustrato)"
              + " and l.historial in (from Historial where fecha= :fecha)  group by l.sustrato.id");
      query.setParameter("fecha", fecha);
      i = query.iterate();
      while (i.hasNext()) {
        Object[] obj = (Object[]) i.next();
        int id = (Integer) obj[0];
        lista.add(id);
        System.out.println(obj[0] + "--" + obj[1]);
      }
      matriz = (Integer[]) lista.toArray(new Integer[0]);
    } finally {
      sesion.close();
    }
    return matriz;
  }

  public List consultaEstadoValvula(Integer[] ids) {
    List lista = null;
    try {
      iniciarOperacion();
      Criteria criteria = sesion.createCriteria(Lectura.class);
      criteria.add(Restrictions.in("id", ids));
      lista = criteria.list();
      for (Object l : lista) {
        Lectura lec = (Lectura) l;
        System.out.println(lec.getId());
        System.out.println(lec.getSustrato().getId());
      }
    } catch (HibernateException e) {
      System.out.println("A ocurrido un error " + e);
    }
    return lista;
  }
  //Prueba de criteria**********************************************

  public void pruebaCriteria() {
    List lista;
    try {
      iniciarOperacion();
      Criteria criteria = sesion.createCriteria(Sustrato.class, "s");
//      criteria.createCriteria("grupo").setProjection(Projections.rowCount());
      criteria.createCriteria("grupo");
//      criteria.setFetchMode("Grupo", FetchMode.JOIN).setProjection(null);
      ProjectionList projList = Projections.projectionList();



      projList.add(Projections.groupProperty("s.id"));
      projList.add(Projections.property("nombre"));
//       projList.add(Projections.groupProperty("s.nombre"));

      projList.add(Projections.count("grupo"));
//     
      criteria.setProjection(projList);

      lista = criteria.list();
      System.out.println(lista);
      Iterator i = lista.iterator();

      if (!i.hasNext()) {
        System.out.println("No hay datos cabron");
      } else {
        while (i.hasNext()) {
          Object[] obj = (Object[]) i.next();
          for (int x = 0; x < obj.length; x++) {
            System.out.println(obj[x]);
          }
          System.out.println("-----------------");
        }
      }
    } finally {
      sesion.close();
    }

  }

  public Integer[] consultaUltHoraCriteria(Date fecha, String[] sustratos) {
    Integer[] matriz = null;
    ArrayList<Integer> lista = new ArrayList<Integer>();
    Iterator i;
    try {
      iniciarOperacion();
      DetachedCriteria idSustrato = DetachedCriteria.forClass(Sustrato.class).add(Restrictions.in("nombre", sustratos)).setProjection(Property.forName("id"));

      DetachedCriteria fechaH = DetachedCriteria.forClass(Historial.class).setProjection(Projections.max("fecha"));

      Criteria criteria = sesion.createCriteria(Lectura.class, "l");
      criteria.createCriteria("sustrato", "s");
      criteria.createCriteria("historial", "h");
      ProjectionList listProy = Projections.projectionList();
      listProy.add(Projections.max("l.id"));
      listProy.add(Projections.groupProperty("s.id"));

      criteria.add(Property.forName("s.id").in(idSustrato));
      criteria.add(Property.forName("h.fecha").in(fechaH));


      criteria.setProjection(listProy);
      i = criteria.list().iterator();

      while (i.hasNext()) {
        Object[] obj = (Object[]) i.next();
        int id = (Integer) obj[0];
        lista.add(id);
        System.out.println(obj[0] + "--" + obj[1]);
      }
      matriz = (Integer[]) lista.toArray(new Integer[0]);
    } finally {
      sesion.close();
    }
    return matriz;
  }
}
